# -*- coding: utf-8 -*-
# @Time    : 2021/2/1 9:42
# @Author  : AkromA
# @File    : db_conn.py
# @Software: PyCharm
# @Info    : 数据库连接类


import pymysql
from sshtunnel import SSHTunnelForwarder


class DBHandle(object):

    def __init__(self):

        self.dbname = "house"

        self.server = SSHTunnelForwarder(
                ("47.96.133.109", 22),  # 跳板机（堡垒机）B配置
                ssh_username="root",
                ssh_password="Hualeihaofang@2020",
                remote_bind_address=("127.0.0.1", 3306)) # 数据库存放服务器C配置

        # 打开数据库连接
        self.server.start()
        self.db_connect = pymysql.connect(host='127.0.0.1',  # 本机主机A的IP
                                         port=self.server.local_bind_port,
                                         user="root",
                                         passwd="5bb856ea56841a92",
                                         db=self.dbname)  # 需要连接的数据库的名称

    # 查询
    def select_sql(self, sql):
        # 使用cursor()方法获取操作游标
        cursor = self.db_connect.cursor(cursor=pymysql.cursors.DictCursor)
        results = ''
        try:
            # 执行SQL语句
            cursor.execute(sql)
            # 获取所有记录列表
            results = cursor.fetchall()
        except Exception as error:
            print('Error: 执行查询失败，%s' % error)
        finally:
            cursor.close()
        return results

    # 新增单条并获取对应id
    def insert_one(self, sql):
        # 使用cursor()方法获取操作游标
        cursor = self.db_connect.cursor()
        results = ''
        try:
            # 执行SQL语句
            cursor.execute(sql)
            self.db_connect.commit()

            # 获取刚插入数据的id
            results = cursor.lastrowid
        except Exception as error:
            print('Error: 新增单条数据失败，%s' % error)
        finally:
            cursor.close()
        return results

    # 批量插入
    # sql模式例：insert into tablename (column1, column2...) value (%s,%s)
    # data模式例：[(1,'python'),(2,'java'),(3,'c'),(4,'php')]，必须为数组
    def insert_muti(self, sql, data):

        cursor = self.db_connect.cursor()
        try:
            cursor.executemany(sql, data)
            self.db_connect.commit()
        except Exception as error:
            print('Error: 新增多条数据失败，%s' % error)
        finally:
            cursor.close()

    # 更新
    def update_by_id(self, sql):

        cursor = self.db_connect.cursor()
        try:
            cursor.execute(sql)
            self.db_connect.commit()
        except Exception as error:
            print('Error: 更新数据失败，%s' % error)
        finally:
            cursor.close()


    # 关闭连接
    def close_db(self):
        ''' 数据库连接关闭 '''
        self.db_connect.close()
        self.server.close()


if __name__ == '__main__':
    # sql = "select * from 06_house where id = 6606;"
    sql = ""
    db_handle = DBHandle()
    print(db_handle.select_sql(sql))
    db_handle.close_db()
